----------#########################  揽收   ####################
with tab_taking as (
    --对于重复扫描的进行重新排序 去重
    select
        billcode, network_code, network_name, scan_type, scan_time, upload_time, consume_time, item_type, status, create_time, update_time, recordid, scanusercode, scanuser, packagecode, pistolid, is_no_in_time --1不及时 0及时
         ,null as   bscan_time --'包号的扫描时间' 
         ,null as   bupload_time
    from (
             select
                 waybill_no     as billcode,     --  运单号
                 scan_site_code as network_code, --  责任网点编码
                 input_site    as network_name, --  责任网点
                 scan_type     as scan_type,    --  操作类型
                 scan_time     as scan_time,    --  操作时间
                 input_time    as upload_time,  --  上传时间
                 cast(abs((unix_timestamp(input_time)-unix_timestamp(scan_time)))/60 as decimal(10,2))  as consume_time, --  上传时长
                 'taking' as item_type,        -- 操作类型
                 1 status,
                 '' create_time,
                 '' update_time,
                 recordid as recordid,         --扫描主键
                 scan_user_code as scanusercode, --  扫描雇员编号
                 scan_user as scanuser,         --  扫描雇员
                 package_code as packagecode,
                 pistol_id as pistolid,          --巴枪编码
                 if(cast(abs((unix_timestamp(input_time)-unix_timestamp(scan_time)))/60 as decimal(10,2))>=40,
                    1,0 )as is_no_in_time      --  上传是否及时
             from
                 jms_dwd.dwd_tab_barscan_taking_base_dt
             where dt='{{ execution_date | cst_ds }}'
         )t
),
----------#########################  发件   ####################
     tab_send_package as
         (
             select
                 billcode, network_code, network_name, scan_type, scan_time, upload_time, consume_time, item_type, status, create_time, update_time, recordid,scanusercode, scanuser, packagecode, pistolid,
                 is_no_in_time --1不及时 0及时
             from
                 (
                     select
                         waybill_no     as billcode,     --  运单号
                         scan_site_code as network_code, --  责任网点编码
                         input_site     as network_name, --  责任网点
                         scan_type      as scan_type,    --  操作类型（发件扫描）
                         scan_time       as scan_time,    --  操作时间
                         input_time      as upload_time,  --  上传时间
                         cast(abs((unix_timestamp(input_time)-unix_timestamp(scan_time)))/60 as decimal(10,2))  as consume_time, --  上传时长
                         'send' as item_type,        -- 操作类型
                         1 status,
                         '' create_time,
                         '' update_time,
                         record_id as recordid,         --扫描主键
                         scan_user_code as scanusercode, --  扫描雇员编号
                         scan_user as scanuser,         --  扫描雇员
                         package_code as packagecode,
                         pistol_id as pistolid,          --巴枪编码
                         if(cast(abs((unix_timestamp(input_time)-unix_timestamp(scan_time)))/60 as decimal(10,2))>=40,
                            1,0 )as is_no_in_time --  上传时长
                     from
                         jms_dwd.dwd_tab_barscan_send_base_dt
                     where dt>=date_add('{{ execution_date | cst_ds }}',-2) and dt<='{{ execution_date | cst_ds }}'  and substr(waybill_no,1,1)='B'
                 )t
         ),
     tab_send_waybill as
         (
---对于重复扫描的进行重新排序
             select
                 billcode, network_code, network_name, scan_type, scan_time, upload_time, consume_time, item_type, status, create_time, update_time, recordid,scanusercode, scanuser, packagecode, pistolid, is_no_in_time --1不及时 0及时
                  ,bscan_time --'包号的扫描时间' 
                  ,bupload_time
             from (

                      select
                          send.waybill_no     as billcode,     --  运单号
                          send.scan_site_code as network_code, --  责任网点编码
                          send.input_site    as network_name, --  责任网点
                          send.scan_type     as scan_type,    --  操作类型（发件扫描）
                          send.scan_time     as scan_time,    --  操作时间
                          send.input_time    as upload_time,  --  上传时间
                          cast(abs((unix_timestamp(send.input_time)-unix_timestamp(send.scan_time)))/60 as decimal(10,2))  as consume_time, --  上传时长 
                          'send' as item_type,        -- 操作类型
                          1 status,
                          '' create_time,
                          '' update_time,
                          send.record_id as recordid,         --扫描主键
                          send.scan_user_code as scanusercode, --  扫描雇员编号
                          send.scan_user as scanuser,         --  扫描雇员
                          send.package_code as packagecode,
                          send.pistol_id as pistolid,          --巴枪编码
                          case when p.is_no_in_time is not NULL and p.is_no_in_time=1 then 1
                               when cast(abs((unix_timestamp(send.input_time)-unix_timestamp(send.scan_time)))/60 as decimal(10,2))>=40 and p.is_no_in_time is NULL then 1
                               else 0 end as is_no_in_time
                              ,p.scan_time  as bscan_time
                              ,p.upload_time as bupload_time
                      from
                          (select * from jms_dwd.dwd_tab_barscan_send_base_dt where  dt='{{ execution_date | cst_ds }}' ) send
                              left join tab_send_package p on (p.billcode=send.package_code  and  p.network_code=send.scan_site_code  and p.scan_time=send.scan_time )
                  ) t
         ),
----------########################## 到件  ######################
     tab_arr_package as
         (
             select * from (
                               select
                                   waybill_no     as billcode,     --  运单号
                                   scan_site_code as network_code, --  责任网点编码
                                   scan_site    as network_name, --  责任网点
                                   scan_type     as scan_type,    --  操作类型（发件扫描）
                                   scan_time     as scan_time,    --  操作时间
                                   input_time    as upload_time,  --  上传时间
                                   cast(abs((unix_timestamp(input_time)-unix_timestamp(scan_time)))/60 as decimal(10,2))  as consume_time, --  上传时长
                                   'arrival' as item_type,            --  操作类型
                                   1 status,
                                   '' create_time,
                                   '' update_time,
                                   record_id as recordid,         --  扫描主键
                                   scan_user_code as scanusercode, --  扫描雇员编号
                                   scan_user as scanuser,         --  扫描雇员
                                   package_code as packagecode,
                                   pistol_id as pistolid,         --  巴枪编码
                                   if(cast(abs((unix_timestamp(input_time)-unix_timestamp(scan_time)))/60 as decimal(10,2))>=40,
                                      1,0 )as is_no_in_time      --  上传时长

                               from
                                   jms_dwd.dwd_tab_barscan_arrival_base_dt
                               where dt>=date_add('{{ execution_date | cst_ds }}',-2) and dt<='{{ execution_date | cst_ds }}' and substr(waybill_no ,1,1)='B'
                           )t
         ),
     tab_arr_waybill as (
--对于重复扫描的进行重新排序
         select
             billcode, network_code, network_name, scan_type, scan_time, upload_time, consume_time, item_type, status, create_time, update_time, recordid, scanusercode, scanuser, packagecode, pistolid, is_no_in_time --1不及时 0及时
              ,bscan_time
              ,bupload_time
         from
             (
                 select
                     send.waybill_no     as billcode,     --  运单号
                     send.scan_site_code as network_code, --  责任网点编码
                     send.scan_site     as network_name, --  责任网点
                     send.scan_type     as scan_type,    --  操作类型（发件扫描）
                     send.scan_time     as scan_time,    --  操作时间
                     send.input_time    as upload_time,  --  上传时间
                     cast(abs((unix_timestamp(send.input_time)-unix_timestamp(send.scan_time)))/60 as decimal(10,2))  as consume_time, --  上传时长
                     'arrival' as item_type,                 --  操作类型
                     1 status,
                     '' create_time,
                     '' update_time,
                     send.record_id as recordid,         --  扫描主键
                     send.scan_user_code as scanusercode, --  扫描雇员编号
                     send.scan_user as scanuser,         --  扫描雇员
                     send.package_code as packagecode,
                     send.pistol_id as pistolid,         --  巴枪编码
                     case when p.is_no_in_time is not NULL and p.is_no_in_time=1 then 1
                          when cast(abs((unix_timestamp(send.input_time)-unix_timestamp(send.scan_time)))/60 as decimal(10,2))>=40 and p.is_no_in_time is NULL then 1
                          else 0 end as is_no_in_time           --  上传时长
                         ,p.scan_time  as bscan_time
                         ,p.upload_time as bupload_time
                 from
                     (select * from jms_dwd.dwd_tab_barscan_arrival_base_dt where  dt='{{ execution_date | cst_ds }}') send
                     left join tab_arr_package p on (p.billcode=send.package_code  and  p.network_code=send.scan_site_code and p.scan_time=send.scan_time)
             ) t1
     ),

----------########################## 派件  ######################

     tab_delivery as (
--对于重复扫描的进行重新排序
         select
             billcode, network_code, network_name, scan_type, scan_time, upload_time, consume_time, item_type, status, create_time, update_time, recordid, scanusercode, scanuser, packagecode, pistolid, is_no_in_time --1不及时 0及时
              ,null as bscan_time
              ,null as bupload_time
         from
             (
                 select
                     waybill_no     as billcode,     --  运单号
                     scan_site_code as network_code, --  责任网点编码
                     scan_site    as network_name, --  责任网点
                     scan_type     as scan_type,    --  操作类型
                     scan_time     as scan_time,    --  操作时间
                     input_time    as upload_time,  --  上传时间
                     cast(abs((unix_timestamp(input_time)-unix_timestamp(scan_time)))/60 as decimal(10,2))  as consume_time, --  上传时长
                     'deliver' as item_type,        --  操作类型
                     1 status,
                     '' create_time,
                     '' update_time,
                     record_id as recordid,         --  扫描主键
                     scan_user_code as scanusercode, --  扫描雇员编号
                     scan_user as scanuser,         --  扫描雇员
                     package_code as packagecode,
                     pistol_id as pistolid,          --巴枪编码
                     if(cast(abs((unix_timestamp(input_time)-unix_timestamp(scan_time)))/60 as decimal(10,2))>=40,
                        1,0 )as is_no_in_time --  上传时长
                 from
                     jms_dwd.dwd_tab_barscan_deliver_base_dt
                 where dt='{{ execution_date | cst_ds }}'
             )t1
     ),

----------########################## sign签件  ######################

     tab_sign as (
--对于重复扫描的进行重新排序
         select
             billcode, network_code, network_name, scan_type, scan_time, upload_time, consume_time, item_type, status, create_time, update_time, recordid, scanusercode, scanuser, packagecode, pistolid, is_no_in_time --1不及时 0及时
              ,null as bscan_time
              ,null as bupload_time
         from (
                  select
                      waybill_no     as billcode,     --  运单号
                      signed_site_code as network_code, --  责任网点编码
                      signed_site    as network_name, --  责任网点
                      '签收'     as scan_type,    --  操作类型
                      signed_time     as scan_time,    --  操作时间
                      input_time    as upload_time,  --  上传时间
                      cast(abs((unix_timestamp(input_time)-unix_timestamp(signed_time)))/60 as decimal(10,2))  as consume_time, --  上传时长
                      'sign' as item_type,        --  操作类型
                      1 status,
                      '' create_time,
                      '' update_time,
                      '' as recordid,         --  扫描主键
                      scan_user_code as scanusercode, --  扫描雇员编号
                      deliver_user as scanuser,         --  扫描雇员
                      '' as packagecode,
                      pistol_id as pistolid,          --巴枪编码
                      if(cast(abs((unix_timestamp(input_time)-unix_timestamp(signed_time)))/60 as decimal(10,2))>=40,
                         1,0 )as is_no_in_time --  上传时长
                  from
                      jms_dwd.dwd_tab_barscan_sign_base_dt
                  where dt='{{ execution_date | cst_ds }}'
              )t1
     ),

----------########################## 代收上传  ######################
----代收上传及时率（代理点）：取代理点收入扫描的数据； 代理点收入没有数据
----代收上传及时率（终端）：取入库、出库、快件取出的数据；
     tab_collect as (
--对于重复扫描的进行重新排序
         select
             billcode, network_code, network_name, scan_type, scan_time, upload_time, consume_time, item_type, status, create_time, update_time, recordid, scanusercode, scanuser, packagecode, pistolid, is_no_in_time --1不及时 0及时
              ,null as bscan_time
              ,null as bupload_time
         from
             (
                 select
                     waybill_no     as billcode,     --  运单号
                     scan_site_code as network_code, --  责任网点编码
                     scan_site    as network_name, --  责任网点
                     scan_type     as scan_type,    --  操作类型(入库扫描,出库扫描,快件取出扫描)
                     scan_time     as scan_time,    --  操作时间
                     input_time    as upload_time,  --  上传时间
                     cast(abs((unix_timestamp(input_time)-unix_timestamp(scan_time)))/60 as decimal(10,2))  as consume_time, --  上传时长
                     'collect' as item_type,        --  操作类型
                     1 status,
                     '' create_time,
                     '' update_time,
                     record_id as recordid,         --  扫描主键
                     scan_user_code as scanusercode, --  扫描雇员编号
                     scan_user as scanuser,         --  扫描雇员
                     package_code as packagecode,
                     pistol_id as pistolid,          --巴枪编码
                     if(cast(abs((unix_timestamp(input_time)-unix_timestamp(scan_time)))/60 as decimal(10,2))>=40,
                        1,0 )as is_no_in_time --  上传时长
                 from
                     jms_dwd.dwd_tab_barscan_collect_base_dt
                 where dt='{{ execution_date | cst_ds }}'
             )t1
     ),

----------########################## 其他上传 .问题件 ######################
     tab_diffcult as (
--对于重复扫描的进行重新排序
         select
             billcode, network_code, network_name, scan_type, scan_time, upload_time, consume_time, item_type, status, create_time, update_time, recordid, scanusercode, scanuser, packagecode, pistolid, is_no_in_time --1不及时 0及时
              ,null as bscan_time
              ,null as bupload_time
         from (
                  select
                      waybill_no     as billcode,     --  运单号
                      scan_site_code as network_code, --  责任网点编码
                      scan_site    as network_name, --  责任网点
                      scan_type     as scan_type,    --  操作类型
                      scan_time     as scan_time,    --  操作时间
                      input_time    as upload_time,  --  上传时间
                      cast(abs((unix_timestamp(input_time)-unix_timestamp(scan_time)))/60 as decimal(10,2))  as consume_time, --  上传时长
                      'diffcult' as item_type,        --  操作类型
                      1 status,
                      '' create_time,
                      '' update_time,
                      record_id as recordid,         --  扫描主键
                      scan_user_code as scanusercode, --  扫描雇员编号
                      scan_user as scanuser,         --  扫描雇员
                      '' as packagecode,
                      pistol_id as pistolid,          --巴枪编码
                      if(cast(abs((unix_timestamp(input_time)-unix_timestamp(scan_time)))/60 as decimal(10,2))>=40,
                         1,0 )as is_no_in_time --  上传时长
                  from
                      jms_dwd.dwd_tab_barscan_difficult_base_dt
                  where dt='{{ execution_date | cst_ds }}'
              )t1
     ),
----------########################## 其他上传 .留仓 ######################
     tab_liucang as (
         select
             billcode, network_code, network_name, scan_type, scan_time, upload_time, consume_time, item_type, status, create_time, update_time, recordid, scanusercode, scanuser, packagecode, pistolid, is_no_in_time --1不及时 0及时
              ,null as bscan_time
              ,null as bupload_time
         from
             (
                 select
                     waybill_no     as billcode,     --  运单号
                     scan_site_code as network_code, --  责任网点编码
                     scan_site    as network_name, --  责任网点
                     scan_type     as scan_type,    --  操作类型
                     scan_time     as scan_time,    --  操作时间
                     input_time    as upload_time,  --  上传时间
                     cast(abs((unix_timestamp(input_time)-unix_timestamp(scan_time)))/60 as decimal(10,2))  as consume_time, --  上传时长
                     'liucang' as item_type,        --  操作类型
                     1 status,
                     '' create_time,
                     '' update_time,
                     record_id as recordid,         --  扫描主键
                     scan_user_code as scanusercode, --  扫描雇员编号
                     scan_user as scanuser,         --  扫描雇员
                     '' as packagecode,
                     pistol_id as pistolid,          --巴枪编码
                     if(cast(abs((unix_timestamp(input_time)-unix_timestamp(scan_time)))/60 as decimal(10,2))>=40,
                        1,0 )as is_no_in_time --  上传时长
                 from
                     jms_dwd.dwd_tab_barscan_other_base_dt
                 where dt='{{ execution_date | cst_ds }}' and scan_type='留仓件入仓'
             )t1
     )


insert overwrite table  jms_dm.dm_upload_in_time_detail_dt partition (dt,intime)
select
    billcode, network_code, network_name, scan_type, scan_time, upload_time, consume_time,
    case when item_type='taking'                        then 'taking'
         when item_type='deliver'                       then 'deliver'
         when item_type='send'                          then 'transfer'
         when item_type='arrival'                       then 'transfer'
         when item_type='diffcult'                      then 'other'
         when item_type='liucang'                       then 'other'
         when item_type='collect' and scan_type in ('入库扫描', '出库扫描', '快件取出扫描')     then 'collect'
         when item_type='sign'                          then 'sign'
        end as item_type
     , status, null as create_time,null as update_time, recordid, scanusercode, scanuser, packagecode
     ,bscan_time,bupload_time , pistolid
     ,ordersource_code as source_code
     ,ordersource_name as source_name
     ,network_type
     ,agent_code  --代理区
     ,agent_name  --代理区名称
     ,manage_code  --管理大区code
     ,manage_name --管理大区name
     ,fran_code  --加盟商code
     ,fran_name --加盟商name
     ,substr(upload_time,1,10 ) as  date_time
     ,substr(upload_time,1,10 ) as  dt
     ,is_no_in_time    as   intime --不推
from (
         SELECT
             t.billcode, network_code, network_name, scan_type, scan_time, upload_time, consume_time,
             status, recordid, scanusercode, scanuser, packagecode
              ,bscan_time,bupload_time , pistolid
              ,os.ordersource_code
              ,if(os.ordersource_name in ('桃花岛','紫金山','逍遥峰','极地湾','麦田圈','苏宁','有赞','七星潭', '唯品会面单'),ordersource_name,'其他') as ordersource_name
              ,item_type
              ,--case when  item_type in ('taking','deliver','collect','sign') then 1
              --     else if( network_type = 4,2,1) 
              --end as network_type
             net1.network_type
              ,substr(t.upload_time,1,10 ) as  dt
              ,is_no_in_time    --不推
              ,net1.agent_code  --代理区
              ,net1.agent_name  --代理区名称
              ,net1.manage_code  --管理大区code
              ,net1.manage_name --管理大区name
              ,net1.fran_code  --加盟商code
              ,net1.fran_name --加盟商name
         from
             (
                 select * from tab_taking
                 union all
                 select * from tab_send_waybill
                 union all
                 select * from tab_arr_waybill
                 union all
                 select * from tab_delivery
                 union all
                 select * from tab_sign
                 union all
                 select * from tab_collect
                 union all
                 select * from tab_diffcult
                 union all
                 select * from tab_liucang
             ) t
                 left join jms_dim.dim_network_whole_massage net1 on net1.code =t.network_code
                 left join jms_dwd.dwd_yl_oms_oms_waybill_incre_dt os
                           on (os.dt>=date_add('{{ execution_date | cst_ds }}',-14) and os.dt<='{{ execution_date | cst_ds }}'  and t.billcode=os.waybill_no )
         where t.network_code is not null
     )tt
    DISTRIBUTE BY pmod(hash(billcode), 300);
--汇总------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------
insert overwrite table jms_dm.dm_upload_in_time_count_dt partition(dt)
select
    scan_date   --日期
     ,net1.manage_code --管理大区
     ,net1.manage_name --管理大区name
     ,net1.agent_code--代理区code
     ,net1.agent_name--代理区名称
     ,net1.fran_code--加盟商code
     ,net1.fran_name--加盟商名称
     ,network_code --责任网点code
     ,net1.name as network_name--责任网点name
     ,taking_num  --揽收及时量
     ,taking_no_num --揽收不及时量
     ,transfer_num  --转运及时量
     ,transfer_no_num --转运不及时量
     ,sign_num  --签收及时量
     ,sign_no_num --签收不及时量
     ,deliver_num  --派件及时量
     ,deliver_no_num --派件不及时量
     ,null as collect_num_proxy    --代收上传及时量(代理点)
     ,null as collect_no_num_proxy --代收不及时量(代理点)
     ,collect_num    --代收上传及时量(终端)
     ,collect_no_num --代收不及时量(终端)
     ,other_num    --其他及时量
     ,other_no_num --其他不及时量
     ,all_cnt    --整体上传及时量
     ,all_no_cnt --整体不上传及时量
     ,scan_date as date_time
     ,least(all_no_cnt * config.fee_per, config.maximum) as fine_sum
     ,source_name --订单来源名称
     ,net1.network_type  --网点类型
     ,null scanusercode
     ,null scanuser
     ,1 as cnt_type  --网点+订单来源+日期
     ,config.maximum
     ,scan_date as dt
from
    (
        select
            scan_date
             ,network_code
             ,source_name
             ,sum(if(item_type='taking' and intime=0,cnt,0))   as taking_num  --揽收及时量
             ,sum(if(item_type='taking' and intime=1,cnt,0))   as taking_no_num --揽收不及时量
             ,sum(if(item_type='transfer' and intime=0,cnt,0)) as transfer_num  --转运及时量
             ,sum(if(item_type='transfer' and intime=1,cnt,0)) as transfer_no_num --转运不及时量
             ,sum(if(item_type='sign' and intime=0,cnt,0))     as sign_num  --签收及时量
             ,sum(if(item_type='sign' and intime=1,cnt,0))     as sign_no_num --签收不及时量
             ,sum(if(item_type='deliver' and intime=0,cnt,0))  as deliver_num  --派件及时量
             ,sum(if(item_type='deliver' and intime=1,cnt,0))  as deliver_no_num --派件不及时量
             ,sum(if(item_type='collect' and intime=0,cnt,0))  as collect_num  --代收上传及时量(终端)
             ,sum(if(item_type='collect' and intime=1,cnt,0))  as collect_no_num --代收不及时量(终端)
             ,sum(if(item_type='other' and intime=0,cnt,0))    as other_num  --其他及时量
             ,sum(if(item_type='other' and intime=1,cnt,0))    as other_no_num --其他不及时量
             ,sum(if(intime=0,cnt,0)) as all_cnt --整体上传及时量
             ,sum(if(intime=1,cnt,0)) as all_no_cnt --整体上传不及时量
             --,sum(if(intime=1,cnt,0))*0.1 as fine_sum   --fee_money_upper
             --,0 as fine_sum   --fee_money_upper
             , '扫描上传及时率' as fee_menu
        from (
                 select
                     dt scan_date
                      , network_code
                      , intime
                      , item_type
                      , source_name
                      , count(1) cnt
                 from jms_dm.dm_upload_in_time_detail_dt
                 where dt  between '{{ execution_date | cst_ds }}' and  '{{ execution_date | cst_ds }}'
                 group by
                     dt
                        , network_code
                        , intime
                        , item_type
                        , source_name
             )s
        group by scan_date,network_code,source_name
    )t1 left join jms_dim.dim_network_whole_massage net1 on net1.code =t1.network_code
    left join(
        select
            fee_per,fee_menu,maximum
        from
            jms_dim.dim_tab_forfeit_config_new
        where statue = 1
        and is_delete = 1
        and '{{ execution_date | cst_ds }}' between fee_start_time and fee_end_time
        and fee_name = '网点扫描上传不及时处罚'
        and fee_menu = '扫描上传及时率'
    ) config on t1.fee_menu = config.fee_menu

union all

select
    scan_date   --日期
     ,net1.manage_code --管理大区
     ,net1.manage_name --管理大区name
     ,net1.agent_code--代理区code
     ,net1.agent_name--代理区名称
     ,net1.fran_code--加盟商code
     ,net1.fran_name--加盟商名称
     ,network_code --责任网点code
     ,net1.name as network_name--责任网点name
     ,taking_num  --揽收及时量
     ,taking_no_num --揽收不及时量
     ,transfer_num  --转运及时量
     ,transfer_no_num --转运不及时量
     ,sign_num  --签收及时量
     ,sign_no_num --签收不及时量
     ,deliver_num  --派件及时量
     ,deliver_no_num --派件不及时量
     ,null as collect_num_proxy    --代收上传及时量(代理点)
     ,null as collect_no_num_proxy --代收不及时量(代理点)
     ,collect_num    --代收上传及时量(终端)
     ,collect_no_num --代收不及时量(终端)
     ,other_num    --其他及时量
     ,other_no_num --其他不及时量
     ,all_cnt    --整体上传及时量
     ,all_no_cnt --整体不上传及时量
     ,scan_date as date_time
     ,least(all_no_cnt * config.fee_per, config.maximum) as fine_sum
     ,null as source_name --订单来源名称
     , net1.network_type  --网点类型
     , scanusercode
     , scanuser
     , 2 as cnt_type
     , config.maximum
     ,scan_date as dt
from
    (
        select
            scan_date
             ,network_code
             ,scanusercode
             ,scanuser
             ,sum(if(item_type='taking' and intime=0,cnt,0))   as taking_num  --揽收及时量
             ,sum(if(item_type='taking' and intime=1,cnt,0))   as taking_no_num --揽收不及时量
             ,sum(if(item_type='transfer' and intime=0,cnt,0)) as transfer_num  --转运及时量
             ,sum(if(item_type='transfer' and intime=1,cnt,0)) as transfer_no_num --转运不及时量
             ,sum(if(item_type='sign' and intime=0,cnt,0))     as sign_num  --签收及时量
             ,sum(if(item_type='sign' and intime=1,cnt,0))     as sign_no_num --签收不及时量
             ,sum(if(item_type='deliver' and intime=0,cnt,0))  as deliver_num  --派件及时量
             ,sum(if(item_type='deliver' and intime=1,cnt,0))  as deliver_no_num --派件不及时量
             ,sum(if(item_type='collect' and intime=0,cnt,0))  as collect_num  --代收上传及时量(终端)
             ,sum(if(item_type='collect' and intime=1,cnt,0))  as collect_no_num --代收不及时量(终端)
             ,sum(if(item_type='other' and intime=0,cnt,0))    as other_num  --其他及时量
             ,sum(if(item_type='other' and intime=1,cnt,0))    as other_no_num --其他不及时量
             ,sum(if(intime=0,cnt,0)) as all_cnt --整体上传及时量
             ,sum(if(intime=1,cnt,0)) as all_no_cnt --整体上传不及时量
             --,sum(if(intime=1,cnt,0))*0.1 as fine_sum   --fee_money_upper
             -- ,0 as fine_sum   --fee_money_upper
             , '扫描上传及时率' as fee_menu
        from (
                 select
                     dt scan_date
                      , network_code
                      , intime
                      , item_type
                      , scanusercode
                      , scanuser
                      , count(1) cnt
                 from jms_dm.dm_upload_in_time_detail_dt
                 where dt  between  '{{ execution_date | cst_ds }}' and  '{{ execution_date | cst_ds }}'
                 group by
                     dt
                        , network_code
                        , intime
                        , item_type
                        , scanusercode
                        , scanuser
             )s
        group by scan_date,network_code,scanusercode,scanuser
    )t1 left join jms_dim.dim_network_whole_massage net1 on net1.code =t1.network_code
    left join(
        select
            fee_per,fee_menu,maximum
        from
            jms_dim.dim_tab_forfeit_config_new
        where statue = 1
        and is_delete = 1
        and '{{ execution_date | cst_ds }}' between fee_start_time and fee_end_time
        and fee_name = '网点扫描上传不及时处罚'
        and fee_menu = '扫描上传及时率'
    ) config on t1.fee_menu = config.fee_menu
    distribute by 1;


